package com.zjht.gmcc.bean.action;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.zjht.gmcc.bean.util.JDBCUtil;
import com.zjht.gmcc.bean.util.MyConstant;
import com.zjht.gmcc.bean.util.MyFormat;
import com.zjht.gmcc.bean.util.MyJDBC;
import com.zjht.gmcc.bean.util.MyParamUtil;

/**
 * 终端限售/停售
 * @author dyh
 */
public class ProductWarnUtil implements java.io.Serializable {
	private static final long serialVersionUID = -2144739265432858846L;

	// 限售/停售类型
	public static final String WARNTYPE_LIMIT = "1"; // 限售
	public static final String WARNTYPE_BAN = "2"; // 停售

	// 限售/停售范围
	public static final String WARNRANGE_PROV = "0"; // 全省
	public static final String WARNRANGE_CITY = "1"; // 全市
	public static final String WARNRANGE_COUNTY = "2"; // 全区/县
	public static final String WARNRANGE_HALL = "3"; // 单店

	// 限售/停售参数主表状态
	public static final String WARNPARA_OK = "1"; // 启用
	public static final String WARNPARA_STOP = "0"; // 停用

	// 限售/停售状态
	public static final String WARN_NEW = "1"; // 禁用
	public static final String WARN_CANCEL = "0"; // 解禁

	// 限售/停售短信发送状态
	public static final String SMS_NO_SEND = "0"; // 未发送
	public static final String SMS_SEND = "1"; // 已发送
	public static final String SMS_WARNCANCEL = "2"; // 已发送解禁短信

	public static final String WARN_TIP = "(标为<font color='red'>红色</font>的机型已超过省移动规定限额，<font color='red'>禁止补货</font>，<a href=\"javascript:showModal('/publish/warn_log.jsp?mode=1')\"><font color='blue'>点击查看详情</font></a>)"; // 显示在jsp页面上的警告字句

	/**
	 * 获取限售/停售类型名称
	 * @author dyh 2010-07-13
	 * @param warntype 取限售/停售类型
	 * @return 限售/停售类型名称
	 */
	public static String getWarnTypeName(String warntype) {
		String warntypename = "";
		if (warntype != null) {
			if (WARNTYPE_LIMIT.equals(warntype)) {
				warntypename = "限售";
			} else if (WARNTYPE_BAN.equals(warntype)) {
				warntypename = "<font color='red'>停售</font>";
			}
		}
		return warntypename;
	}

	/**
	 * 获取限售/停售范围名称
	 * @author dyh 2010-07-13
	 * @param warnrange 取限售/停售范围
	 * @return 限售/停售范围名称
	 */
	public static String getWarnRangeName(String warnrange) {
		String warnrangename = "";
		if (warnrange != null) {
			if (WARNRANGE_PROV.equals(warnrange)) {
				warnrangename = "全省";
			} else if (WARNRANGE_CITY.equals(warnrange)) {
				warnrangename = "全市";
			} else if (WARNRANGE_COUNTY.equals(warnrange)) {
				warnrangename = "全区/县";
			} else if (WARNRANGE_HALL.equals(warnrange)) {
				warnrangename = "单店";
			}
		}
		return warnrangename;
	}

	/**
	 * 检测参数是否已重复
	 * @author dyh 2010-07-15
	 * @param productsystem
	 * @param producttype
	 * @param warntype
	 * @param warnrange
	 * @param paraid -1表示新增
	 * @return 是否已存在于数据库
	 */
	public static boolean isWarnParaExist(String productsystem, String producttype, String warntype, String warnrange, int paraid) {
		boolean result = true;
		if (productsystem != null && productsystem.length() > 0 && producttype != null && producttype.length() > 0 && warntype != null
				&& warntype.length() > 0 && warnrange != null && warnrange.length() > 0) {
			MyJDBC myJDBC = null;
			try {
				myJDBC = new MyJDBC();
				String strSQL = "SELECT count(paraid) FROM ProductWarnPara WHERE productsystem=? AND producttype=? AND warntype=? AND warnrange=?";
				if (paraid > 0) {
					strSQL += " AND paraid<>?";
				}
				PreparedStatement ps = myJDBC.prepareStatement(strSQL);
				ps.setString(1, productsystem);
				ps.setString(2, producttype);
				ps.setString(3, warntype);
				ps.setString(4, warnrange);
				if (paraid > 0) {
					ps.setInt(5, paraid);
				}
				ResultSet rs = ps.executeQuery();
				if (rs.next()) {
					result = rs.getInt(1) > 0;
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				MyJDBC.close(myJDBC);
			}
		}
		return result;
	}

	/**
	 * 检测终端是否允许补货或配送
	 * @author dyh 2010-07-19
	 * @param productid 产品ID
	 * @param hall 服务厅编码
	 * @param cityprovider 市平台编码
	 * @return 是否允许补货或配送
	 */
	public static boolean canFill(String productid, String hall, String cityprovider) {
		return canFill(productid, hall, MyParamUtil.getChannelTypeByOrgaCode(hall), cityprovider, MyParamUtil.getChannelTypeByOrgaCode(cityprovider));

	}

	/**
	 * 检测终端是否允许补货或配送
	 * @author dyh 2010-10-16
	 * @param productid 产品ID
	 * @param hall 服务厅编码
	 * @param orgaChannelType 组织渠道类型
	 * @param cityprovider 市平台编码
	 * @param providerChannelType 市平台渠道类型
	 * @return 是否允许补货或配送
	 */
	public static boolean canFill(String productid, String hall, String orgaChannelType, String cityprovider, String providerChannelType) {
		boolean result = false;
		if (!MyFormat.isStrNull(hall) && !MyFormat.isStrNull(productid) && !MyFormat.isStrNull(cityprovider)) {
			String provider = MyParamUtil.getParentOrgaCode(cityprovider);// 省平台编码
			if (MyFormat.isStrNull(provider))
				return result;
			int areaid = MyParamUtil.getOrgaAreaID(hall);
			String county = MyParamUtil.getParentOrgaCode(hall);// 区移动
			if (MyFormat.isStrNull(orgaChannelType) || MyFormat.isStrNull(providerChannelType) || !MyFormat.checkAreaID(areaid)
					|| MyFormat.isStrNull(county))
				return result;

			String productsystem = "";
			String producttype = "";
			String brandcode = "";
			double price = 0;
			MyJDBC myJDBC = null;
			ResultSet rs = null;
			try {
				myJDBC = new MyJDBC();
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT productsystem,producttype,brandcode,price FROM productgroup WHERE productid=?");
				PreparedStatement ps = myJDBC.prepareStatement(sql.toString());
				ps.setString(1, productid);
				rs = ps.executeQuery();
				if (rs.next()) {
					productsystem = rs.getString(1);
					producttype = rs.getString(2);
					brandcode = rs.getString(3);
					price = rs.getDouble(4);
				} else {
					return result;
				}

				// 一、如果为免禁名单，则返回true
				sql = new StringBuilder();
				sql.append(" SELECT 1 FROM DUAL WHERE  EXISTS (SELECT 1 FROM PRODUCTWARNAVOID WHERE status='1' ");
				sql
						.append(" AND (hall=? OR (hall IS NULL AND (areaid IS NULL OR areaid=0 OR areaid =?) AND (channeltype=? OR channeltype IS NULL) AND (county IS NULL OR county=?))) ");
				sql.append(" AND (productid=? OR (productid IS NULL AND ");
				sql.append(" ((PRODUCTSYSTEM=? AND (PRODUCTTYPE IS NULL OR (PRODUCTTYPE=? AND (brandcode=? OR brandcode IS NULL)))) ");
				sql.append(" OR (PRODUCTSYSTEM IS NULL AND (PRODUCTTYPE IS NULL OR (PRODUCTTYPE=? AND (brandcode=? OR brandcode IS NULL))))) ");
				sql.append(" AND ((pricelow IS NULL AND pricehigh IS NULL) OR (pricelow=0 AND pricehigh=0)  ");
				sql.append(" OR (pricelow>0 AND (pricehigh=0 OR pricehigh IS NULL)  AND ?>=pricelow)  ");
				sql.append(" OR ((pricelow=0 OR pricelow IS NULL) AND pricehigh>0 AND ?<=pricehigh)  ");
				sql.append(" OR (pricelow>0 AND pricehigh>0 AND ? BETWEEN pricelow AND pricehigh)))) ");
				sql.append("  AND (provider=? OR (provider IS NULL AND (providerchanneltype=? OR providerchanneltype IS NULL))))");

				ps = myJDBC.prepareStatement(sql.toString());
				ps.setString(1, hall);
				ps.setInt(2, areaid);
				ps.setString(3, orgaChannelType);
				ps.setString(4, county);
				ps.setString(5, productid);
				ps.setString(6, productsystem);
				ps.setString(7, producttype);
				ps.setString(8, brandcode);
				ps.setString(9, producttype);
				ps.setString(10, brandcode);
				ps.setDouble(11, price);
				ps.setDouble(12, price);
				ps.setDouble(13, price);
				ps.setString(14, provider);
				ps.setString(15, providerChannelType);
				rs = ps.executeQuery();
				if (rs.next()) {
					return true;
				}

				// 二、如果为禁售，则返回false
				sql = new StringBuilder();
				sql.append(" SELECT 1 FROM DUAL WHERE  EXISTS (SELECT 1 FROM PRODUCTWARN  WHERE status=? AND warntype=? ");
				sql
						.append(" AND (hall=? OR (hall IS NULL AND (areaid IS NULL OR areaid=0 OR areaid =?) AND (channeltype=? OR channeltype IS NULL) AND (county IS NULL OR county=?))) ");
				sql.append(" AND (productid=? OR (productid IS NULL AND ");
				sql.append(" ((PRODUCTSYSTEM=? AND (PRODUCTTYPE IS NULL OR (PRODUCTTYPE=? AND (brandcode=? OR brandcode IS NULL)))) ");
				sql.append(" OR (PRODUCTSYSTEM IS NULL AND (PRODUCTTYPE IS NULL OR (PRODUCTTYPE=? AND (brandcode=? OR brandcode IS NULL))))) ");
				sql.append(" AND ((pricelow IS NULL AND pricehigh IS NULL) OR (pricelow=0 AND pricehigh=0)  ");
				sql.append(" OR (pricelow>0 AND (pricehigh=0 OR pricehigh IS NULL)  AND ?>=pricelow)  ");
				sql.append(" OR ((pricelow=0 OR pricelow IS NULL) AND pricehigh>0 AND ?<=pricehigh)  ");
				sql.append(" OR (pricelow>0 AND pricehigh>0 AND ? BETWEEN pricelow AND pricehigh)))) ");
				sql.append("  AND (provider=? OR (provider IS NULL AND (providerchanneltype=? OR providerchanneltype IS NULL))))");
				ps = myJDBC.prepareStatement(sql.toString());
				ps.setString(1, WARN_NEW);
				ps.setString(2, WARNTYPE_BAN);
				ps.setString(3, hall);
				ps.setInt(4, areaid);
				ps.setString(5, orgaChannelType);
				ps.setString(6, county);
				ps.setString(7, productid);
				ps.setString(8, productsystem);
				ps.setString(9, producttype);
				ps.setString(10, brandcode);
				ps.setString(11, producttype);
				ps.setString(12, brandcode);
				ps.setDouble(13, price);
				ps.setDouble(14, price);
				ps.setDouble(15, price);
				ps.setString(16, provider);
				ps.setString(17, providerChannelType);
				rs = ps.executeQuery();
				if (rs.next()) {
					return false;
				}

				// 三、限售库存
				sql = new StringBuilder();
				sql.append("SELECT w.warnrange,d.amount FROM PRODUCTWARNPARA w,PRODUCTWARNPARADETAIL d ");
				sql.append(" WHERE w.paraid=d.paraid AND w.status='1' AND d.status='1' AND w.warntype=?  ");
				sql.append(" AND (d.areaid=? OR d.areaid=0) AND w.PRODUCTSYSTEM=? AND w.PRODUCTTYPE=? ORDER BY w.warnrange DESC ");
				ps = myJDBC.prepareStatement(sql.toString());
				ps.setString(1, WARNTYPE_LIMIT);
				ps.setInt(2, areaid);
				ps.setString(3, productsystem);
				ps.setString(4, producttype);
				String warnrange = "";
				int amount = 0;
				int imeidepot = 0;
				rs = ps.executeQuery();
				while (rs.next()) {
					warnrange = rs.getString(1);
					amount = rs.getInt(2);
					if (WARNRANGE_CITY.equals(warnrange)) {// 全市限售
						imeidepot = MyParamUtil.getIMEIDepot(productid, areaid);
					} else if (WARNRANGE_HALL.equals(warnrange)) {// 单店限售
						imeidepot = MyParamUtil.getIMEIDepot(productid, hall);
					}
					if (imeidepot >= amount) {
						return false;
					}
				}
				result = true;

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					if (rs != null) {
						rs.close();
						rs = null;
					}
				} catch (Exception x) {

				}
				MyJDBC.close(myJDBC);
			}
		}
		return result;
	}

	/**
	 * 对超过警报限制的文字进行标红
	 * @author dyh 2010-07-21
	 * @param canFill 是否正常
	 * @param str 文字
	 * @return 对超过警报限制的文字进行标红
	 */
	public static String getWarnStyle(boolean canFill) {
		return (canFill ? "" : "style='color:red;text-decoration:line-through'");
	}

	/**
	 * 获取价格段名称
	 * @author dyh 2010-07-21
	 * @param price 组织编码（任何组织）
	 * @param pricemode 1-以上，2-以下
	 * @return 价格段名称
	 */
	public static String getPriceName(int pricelow, int pricehigh) {
		String priceText = "";
		if (pricelow != 0 && pricehigh != 0) {
			if (pricelow > pricehigh) {
			} else {
				priceText = pricelow + "元至" + pricehigh + "元";
			}
		} else if (pricelow == 0 && pricehigh == 0) {
			priceText = "无";
		} else {
			if (pricelow == 0) {
				priceText = pricehigh + "元以下";
			} else {
				priceText = pricelow + "元以上";
			}
		}
		return priceText;
	}

	/**
	 * 每日自动禁售
	 * @author dyh 2010-09-07
	 * @param conn
	 */
	public static void stopProduct() {
		MyJDBC myJDBC = null;
		try {
			myJDBC = new MyJDBC();

			// 一、解禁限售IMEI
			myJDBC.beginTransaction();
			String opercode = "ADMIN";
			String warnstatus = WARN_NEW;
			String warntype = WARNTYPE_LIMIT;

			StringBuilder sql = new StringBuilder();
			sql.append("SELECT warnid FROM PRODUCTWARN WHERE status=? AND warntype=?");
			PreparedStatement ps = myJDBC.prepareStatement(sql);
			ps.setString(1, warnstatus);
			ps.setString(2, warntype);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				cancelProductWarn(myJDBC.getConnection(), rs.getInt(1), opercode);
			}
			rs.close();
			rs = null;
			myJDBC.commitTransaction();

			// 二、停售
			String saledate = "";
			String dailyflag = "";// 是否动态参数
			warntype = WARNTYPE_BAN;

			// 停售标准
			sql = new StringBuilder();
			sql.append("SELECT NVL(d.amount,0) FROM PRODUCTWARNPARA w,PRODUCTWARNPARADETAIL d WHERE w.paraid=d.paraid ");
			sql
					.append(" AND w.status=? AND w.warntype=? AND w.warnrange=? AND w.dailyflag=? AND w.PRODUCTSYSTEM=? AND w.PRODUCTTYPE=? AND d.areaid=?");
			PreparedStatement ps_standard = myJDBC.prepareStatement(sql);
			ps_standard.setString(1, warnstatus);
			ps_standard.setString(2, warntype);

			// 停售标准是否存在
			sql = new StringBuilder();
			sql.append("SELECT 1 FROM PRODUCTWARNPARA WHERE status=? AND warntype=? AND warnrange=? AND dailyflag=?");
			PreparedStatement ps_check = myJDBC.prepareStatement(sql);
			ps_check.setString(1, warnstatus);
			ps_check.setString(2, warntype);

			ResultSet rs_standard = null;

			String warnrange = "";
			int areaid = 0;
			String brandcode = "";
			String productid = "";
			String productsystem = "";
			String producttype = "";
			int salequantity, standardamount;
			String orgacode = "";

			boolean hasWarnPara = false;

			// 根据dailyflag值遍历
			for (int i = 0; i <= 1; i++) {
				dailyflag = String.valueOf(i);

				saledate = MyFormat.getYesterday();
				if (i > 0)
					saledate = saledate.substring(0, 4) + "-01-01";

				hasWarnPara = false;
				ps_standard.setString(4, dailyflag);

				// 1、全市停售
				warnrange = WARNRANGE_CITY;
				ps_check.setString(3, warnrange);
				ps_check.setString(4, dailyflag);
				rs = ps_check.executeQuery();
				if (rs.next()) {
					hasWarnPara = true;
				}
				rs.close();
				rs = null;

				if (hasWarnPara) {
					myJDBC.beginTransaction();
					ps_standard.setString(3, warnrange);

					// 超限的地市、机型信息
					sql = new StringBuilder();
					sql.append("SELECT s.areaid,");
					if (i == 0)
						sql.append("g.brandcode,g.productid,");
					sql.append("g.PRODUCTSYSTEM,g.PRODUCTTYPE,SUM(s.quanitity) quantity FROM RTP_SALE s,PRODUCTGROUP g ");
					sql.append(" WHERE s.productid=g.productid AND ");
					if (i == 0)
						sql.append(" s.saledate=? ");
					else
						sql.append(" s.saledate >=? ");

					// 必须排除免禁条件
					sql.append(" AND NOT EXISTS (SELECT 1 FROM PRODUCTWARNAVOID v,ORGANIZA h,ORGANIZA p ");
					sql.append(" WHERE v.status='1' AND s.orgacode=h.orgacode AND s.plantcode=p.orgacode AND h.orgatypeid=4 AND p.orgatypeid=5 ");
					sql
							.append(" AND (v.hall=h.orgacode OR (v.hall IS NULL AND (h.areaid=v.areaid OR v.areaid=0) AND (v.channeltype IS NULL OR v.channeltype=h.channeltype) AND (v.county IS NULL OR v.county=h.parentorgacode)))");
					sql.append(" AND (g.productid=v.productid OR (v.productid IS NULL AND (v.brandcode IS NULL OR g.brandcode=v.brandcode) ");
					sql.append(" AND (v.PRODUCTSYSTEM IS NULL OR g.PRODUCTSYSTEM=v.PRODUCTSYSTEM) ");
					sql.append(" AND (v.PRODUCTTYPE IS NULL OR g.PRODUCTTYPE=v.PRODUCTTYPE) ");
					sql.append(" AND ( (v.pricelow IS NULL AND v.pricehigh IS NULL) OR (v.pricelow=0 AND v.pricehigh=0) ");
					sql.append(" OR (v.pricelow>0 AND (v.pricehigh=0 OR v.pricehigh IS NULL)  AND g.price>=v.pricelow) ");
					sql.append(" OR ((v.pricelow=0 OR v.pricelow IS NULL) AND v.pricehigh>0 AND g.price<=v.pricehigh) ");
					sql.append(" OR (v.pricelow>0 AND v.pricehigh>0 AND g.price BETWEEN v.pricelow AND v.pricehigh)))) ");
					sql
							.append(" AND (p.orgacode=v.provider OR (v.provider IS NULL  AND (v.providerchanneltype=p.channeltype OR v.providerchanneltype IS NULL))))");

					sql.append(" GROUP BY s.areaid,");
					if (i == 0)
						sql.append("g.brandcode,g.productid,");
					sql.append("g.PRODUCTSYSTEM,g.PRODUCTTYPE ");
					sql.append(" HAVING SUM(s.quanitity)>(SELECT d.amount FROM PRODUCTWARNPARA w,PRODUCTWARNPARADETAIL d WHERE  ");
					sql.append(" w.PRODUCTSYSTEM=g.PRODUCTSYSTEM AND w.PRODUCTTYPE=g.PRODUCTTYPE AND w.paraid=d.paraid AND s.areaid=d.areaid ");
					sql.append(" AND w.status=? AND w.warntype=? AND w.warnrange=? AND w.dailyflag=? AND d.status='1') ");
					ps = myJDBC.prepareStatement(sql);
					ps.setString(1, saledate);
					ps.setString(2, warnstatus);
					ps.setString(3, warntype);
					ps.setString(4, warnrange);
					ps.setString(5, dailyflag);
					rs = ps.executeQuery();
					brandcode = null;
					productid = null;
					while (rs.next()) {
						areaid = rs.getInt("areaid");
						if (i == 0) {
							brandcode = rs.getString("brandcode");
							productid = rs.getString("productid");
						}
						productsystem = rs.getString("productsystem");
						producttype = rs.getString("producttype");
						salequantity = rs.getInt("quantity");

						standardamount = 0;
						ps_standard.setString(5, productsystem);
						ps_standard.setString(6, producttype);
						ps_standard.setInt(7, areaid);
						rs_standard = ps_standard.executeQuery();
						if (rs_standard.next()) {
							standardamount = rs_standard.getInt(1);
						}
						rs_standard.close();
						rs_standard = null;
						if (standardamount == 0)
							continue;
						else {
							stopProduct(myJDBC.getConnection(), warntype, areaid, null, null, productsystem, producttype, brandcode, productid, null,
									null, null, 0, 0, (i == 0 ? saledate : null), salequantity, standardamount, opercode);
						}
					}
					myJDBC.commitTransaction();
					ps = null;
					rs.close();
					rs = null;
				}

				// 2、单店停售
				warnrange = WARNRANGE_HALL;
				hasWarnPara = false;
				ps_check.setString(3, warnrange);
				rs = ps_check.executeQuery();
				if (rs.next()) {
					hasWarnPara = true;
				}
				rs.close();
				rs = null;
				if (hasWarnPara) {

					myJDBC.beginTransaction();
					ps_standard.setString(3, warnrange);

					// 超限的地市、机型信息
					sql = new StringBuilder();
					sql.append("SELECT s.areaid,");
					if (i == 0)
						sql.append("g.brandcode,g.productid,");
					sql.append("s.orgacode,g.PRODUCTSYSTEM,g.PRODUCTTYPE,SUM(s.quanitity) quantity FROM RTP_SALE s,PRODUCTGROUP g ");
					sql.append(" WHERE s.productid=g.productid AND ");
					if (i == 0)
						sql.append(" s.saledate=? ");
					else
						sql.append(" s.saledate >=? ");

					// 必须排除免禁条件
					sql.append(" AND NOT EXISTS (SELECT 1 FROM PRODUCTWARNAVOID v,ORGANIZA h,ORGANIZA p ");
					sql.append(" WHERE v.status='1' AND s.orgacode=h.orgacode AND s.plantcode=p.orgacode AND h.orgatypeid=4 AND p.orgatypeid=5 ");
					sql
							.append(" AND (v.hall=h.orgacode OR (v.hall IS NULL AND (h.areaid=v.areaid OR v.areaid=0) AND (v.channeltype IS NULL OR v.channeltype=h.channeltype) AND (v.county IS NULL OR v.county=h.parentorgacode)))");
					sql.append(" AND (g.productid=v.productid OR (v.productid IS NULL AND (v.brandcode IS NULL OR g.brandcode=v.brandcode) ");
					sql.append(" AND (v.PRODUCTSYSTEM IS NULL OR g.PRODUCTSYSTEM=v.PRODUCTSYSTEM) ");
					sql.append(" AND (v.PRODUCTTYPE IS NULL OR g.PRODUCTTYPE=v.PRODUCTTYPE) ");
					sql.append(" AND ( (v.pricelow IS NULL AND v.pricehigh IS NULL) OR (v.pricelow=0 AND v.pricehigh=0) ");
					sql.append(" OR (v.pricelow>0 AND (v.pricehigh=0 OR v.pricehigh IS NULL)  AND g.price>=v.pricelow) ");
					sql.append(" OR ((v.pricelow=0 OR v.pricelow IS NULL) AND v.pricehigh>0 AND g.price<=v.pricehigh) ");
					sql.append(" OR (v.pricelow>0 AND v.pricehigh>0 AND g.price BETWEEN v.pricelow AND v.pricehigh)))) ");
					sql
							.append(" AND (p.orgacode=v.provider OR (v.provider IS NULL  AND (v.providerchanneltype=p.channeltype OR v.providerchanneltype IS NULL))))");

					sql.append(" GROUP BY s.areaid,");
					if (i == 0)
						sql.append("g.brandcode,g.productid,");
					sql.append(" s.orgacode,g.PRODUCTSYSTEM,g.PRODUCTTYPE ");
					sql.append(" HAVING SUM(s.quanitity)>(SELECT d.amount FROM PRODUCTWARNPARA w,PRODUCTWARNPARADETAIL d WHERE  ");
					sql.append(" w.PRODUCTSYSTEM=g.PRODUCTSYSTEM AND w.PRODUCTTYPE=g.PRODUCTTYPE AND w.paraid=d.paraid AND s.areaid=d.areaid ");
					sql.append(" AND w.status=? AND w.warntype=? AND w.warnrange=? AND w.dailyflag=? AND d.status='1') ");
					ps = myJDBC.prepareStatement(sql);
					ps.setString(1, saledate);
					ps.setString(2, warnstatus);
					ps.setString(3, warntype);
					ps.setString(4, warnrange);
					ps.setString(5, dailyflag);
					rs = ps.executeQuery();
					brandcode = null;
					productid = null;
					while (rs.next()) {
						areaid = rs.getInt("areaid");
						if (i == 0) {
							brandcode = rs.getString("brandcode");
							productid = rs.getString("productid");
						}
						orgacode = rs.getString("orgacode");
						productsystem = rs.getString("productsystem");
						producttype = rs.getString("producttype");
						salequantity = rs.getInt("quantity");

						standardamount = 0;
						ps_standard.setString(5, productsystem);
						ps_standard.setString(6, producttype);
						ps_standard.setInt(7, areaid);
						rs_standard = ps_standard.executeQuery();
						if (rs_standard.next()) {
							standardamount = rs_standard.getInt(1);
						}
						rs_standard.close();
						rs_standard = null;
						if (standardamount == 0)
							continue;
						else {
							stopProduct(myJDBC.getConnection(), warntype, areaid, null, orgacode, productsystem, producttype, brandcode, productid,
									null, null, null, 0, 0, (i == 0 ? saledate : null), salequantity, standardamount, opercode);
						}
					}
					myJDBC.commitTransaction();
					ps = null;
					rs.close();
					rs = null;
				}
			}
			ps_standard = null;
			sql = null;
		} catch (Exception e) {
			e.printStackTrace();
			myJDBC.rollbackTransaction();
		} finally {
			MyJDBC.close(myJDBC);
		}
	}

	/**
	 * 限售/禁售
	 * @author dyh 2010-09-03
	 * @param conn
	 * @param warntype
	 * @param warnrange
	 * @param areaid
	 * @param county
	 * @param hall
	 * @param productsystem
	 * @param producttype
	 * @param brandcode
	 * @param productid
	 * @param channeltype
	 * @param providerchanneltype
	 * @param provider
	 * @param pricelow
	 * @param pricehigh
	 * @param opercode
	 * @param saledate
	 * @param salequantity
	 * @param standardamount
	 * @return 禁售IMEI数量(如果数量为MyConstant.INT_NULL，表示禁售失败)
	 */
	public static int stopProduct(Connection conn, String warntype, int areaid, String county, String hall, String productsystem, String producttype,
			String brandcode, String productid, String channeltype, String providerchanneltype, String provider, int pricelow, int pricehigh,
			String saledate, int salequantity, int standardamount, String opercode) {
		int imeicount = MyConstant.INT_NULL;

		// 必填参数
		if (pricelow < 0 || pricehigh < 0 || (pricelow > pricehigh && pricehigh > 0))// 价格段条件非法
			return imeicount;
		if (MyFormat.isStrNull(warntype) || MyConstant.SELECT_FIRST_VALUE.equals(warntype))
			return imeicount;
		if (areaid >= MyConstant.AREA_MAX || areaid < MyConstant.INT_NULL)
			return imeicount;
		if (areaid == MyConstant.INT_NULL)
			areaid = 0;
		String warnrange = "";

		// 规格化参数
		if (MyFormat.isStrNull(county) || MyConstant.SELECT_FIRST_VALUE.equals(county))
			county = "";

		if (MyFormat.isStrNull(hall) || MyConstant.SELECT_FIRST_VALUE.equals(hall))
			hall = "";

		if (areaid == MyConstant.AREAID_GMCC) {// 如果是全省，则区/县和服务厅必须为空
			county = "";
			hall = "";
			warnrange = WARNRANGE_PROV;
		} else if (!MyFormat.isStrNull(hall)) {// 如果服务厅有值，则区/县和渠道类型必须为空
			county = "";
			channeltype = "";
			warnrange = WARNRANGE_HALL;
		} else if (!MyFormat.isStrNull(county)) {
			warnrange = WARNRANGE_COUNTY;
		} else
			warnrange = WARNRANGE_CITY;

		if (MyFormat.isStrNull(channeltype) || MyConstant.SELECT_FIRST_VALUE.equals(channeltype))
			channeltype = "";
		if (MyFormat.isStrNull(productsystem) || MyConstant.SELECT_FIRST_VALUE.equals(productsystem))
			productsystem = "";
		if (MyFormat.isStrNull(producttype) || MyConstant.SELECT_FIRST_VALUE.equals(producttype))
			producttype = "";
		if (MyFormat.isStrNull(brandcode) || MyConstant.SELECT_FIRST_VALUE.equals(brandcode))
			brandcode = "";
		if (MyFormat.isStrNull(productid) || MyConstant.SELECT_FIRST_VALUE.equals(productid))
			productid = "";
		if (MyFormat.isStrNull(providerchanneltype) || MyConstant.SELECT_FIRST_VALUE.equals(providerchanneltype))
			providerchanneltype = "";
		if (MyFormat.isStrNull(provider) || MyConstant.SELECT_FIRST_VALUE.equals(provider))
			provider = "";
		if (MyFormat.isStrNull(opercode) || MyConstant.SELECT_FIRST_VALUE.equals(opercode))
			opercode = "ADMIN";
		if (MyFormat.isStrNull(saledate) || MyConstant.SELECT_FIRST_VALUE.equals(saledate))
			saledate = "";

		try {
			if (!JDBCUtil.isConnected(conn)) {
				return imeicount;
			}
			int warnid = MyParamUtil.getSEQ(conn, "SEQ_PRDWARNID");
			StringBuilder sql = new StringBuilder();

			// 一、 插入禁/限售日志明细表
			sql
					.append("INSERT INTO PRODUCTWARNDETAIL SELECT ?,i.productid,i.orgacode,i.colorid,i.IMEI,i.provorgacode FROM IMEI i WHERE i.saleflag='1' ");

			// 条件1：服务厅组织
			if (WARNRANGE_HALL.equals(warnrange)) {
				sql.append(" AND i.orgacode =? ");
			} else {
				sql.append(" AND i.orgacode IN (SELECT orgacode FROM organiza WHERE orgatypeid=? ");
				if (WARNRANGE_CITY.equals(warnrange)) {
					sql.append(" AND areaid=? ");
				}
				if (!MyFormat.isStrNull(channeltype)) {
					sql.append(" AND channeltype=? ");
				}
				if (!MyFormat.isStrNull(county)) {
					sql.append(" AND parentorgacode=? ");
				}
				sql.append(") ");
			}

			// 条件2：产品ID
			if (!MyFormat.isStrNull(productid)) {
				sql.append(" AND i.productid =? ");
			} else {
				StringBuilder sql_product = new StringBuilder();
				if ((!MyFormat.isStrNull(productsystem))) {
					sql_product.append(" productsystem=? ");
				}
				if ((!MyFormat.isStrNull(producttype))) {
					if (sql_product.length() > 0)
						sql_product.append(" AND ");
					sql_product.append(" producttype=? ");
				}
				if ((!MyFormat.isStrNull(brandcode))) {
					if (sql_product.length() > 0)
						sql_product.append(" AND ");
					sql_product.append(" brandcode=? ");
				}
				if (pricelow > 0 || pricehigh > 0) {
					if (sql_product.length() > 0)
						sql_product.append(" AND ");
					if (pricelow > 0 && pricehigh > 0)
						sql_product.append(" price BETWEEN ? AND ? ");
					else if (pricelow > 0)
						sql_product.append(" price >= ? ");
					else
						sql_product.append(" price <= ? ");
				}
				if (sql_product.length() > 0) {
					sql.append(" AND i.productid IN (SELECT productid FROM productgroup WHERE ");
					sql.append(sql_product);
					sql.append(") ");
				}
			}

			// 条件3：供货省平台
			if (!MyFormat.isStrNull(provider)) {
				sql.append(" AND i.provorgacode =? ");
			} else if (!MyFormat.isStrNull(providerchanneltype)) {
				sql.append(" AND i.provorgacode IN (SELECT orgacode FROM organiza WHERE orgatypeid=? AND channeltype=?) ");
			}

			// 条件4：必须排除免禁条件
			sql.append(" AND NOT EXISTS (SELECT 1 FROM PRODUCTWARNAVOID v,ORGANIZA h,ORGANIZA p,PRODUCTGROUP g ");
			sql.append(" WHERE v.status='1' AND i.saleflag='1' AND i.productid=g.productid ");
			sql.append(" AND i.orgacode=h.orgacode AND i.provorgacode=p.orgacode AND h.orgatypeid=? AND p.orgatypeid=? ");
			sql
					.append(" AND (v.hall=h.orgacode OR (v.hall IS NULL AND (h.areaid=v.areaid OR v.areaid=0) AND (v.channeltype IS NULL OR v.channeltype=h.channeltype) AND (v.county IS NULL OR v.county=h.parentorgacode)))");
			sql.append(" AND (g.productid=v.productid OR (v.productid IS NULL AND (v.brandcode IS NULL OR g.brandcode=v.brandcode) ");
			sql.append(" AND (v.PRODUCTSYSTEM IS NULL OR g.PRODUCTSYSTEM=v.PRODUCTSYSTEM) ");
			sql.append(" AND (v.PRODUCTTYPE IS NULL OR g.PRODUCTTYPE=v.PRODUCTTYPE) ");
			sql.append(" AND ( (v.pricelow IS NULL AND v.pricehigh IS NULL) OR (v.pricelow=0 AND v.pricehigh=0) ");
			sql.append(" OR (v.pricelow>0 AND (v.pricehigh=0 OR v.pricehigh IS NULL)  AND g.price>=v.pricelow) ");
			sql.append(" OR ((v.pricelow=0 OR v.pricelow IS NULL) AND v.pricehigh>0 AND g.price<=v.pricehigh) ");
			sql.append(" OR (v.pricelow>0 AND v.pricehigh>0 AND g.price BETWEEN v.pricelow AND v.pricehigh)))) ");
			sql
					.append(" AND (p.orgacode=v.provider OR (v.provider IS NULL  AND (v.providerchanneltype=p.channeltype OR v.providerchanneltype IS NULL))))");

			PreparedStatement ps = conn.prepareStatement(sql.toString());
			int index = 1;

			ps.setInt(index++, warnid);

			// 条件1：服务厅组织
			if (WARNRANGE_HALL.equals(warnrange)) {
				ps.setString(index++, hall);
			} else {
				ps.setInt(index++, MyConstant.ORGTYPE_HALL);
				if (WARNRANGE_CITY.equals(warnrange)) {
					ps.setInt(index++, areaid);
				}
				if (!MyFormat.isStrNull(channeltype)) {
					ps.setString(index++, channeltype);
				}
				if (!MyFormat.isStrNull(county)) {
					ps.setString(index++, county);
				}
			}

			// 条件2：产品ID
			if (!MyFormat.isStrNull(productid)) {
				ps.setString(index++, productid);
			} else {
				if ((!MyFormat.isStrNull(productsystem))) {
					ps.setString(index++, productsystem);
				}
				if ((!MyFormat.isStrNull(producttype))) {
					ps.setString(index++, producttype);
				}
				if ((!MyFormat.isStrNull(brandcode))) {
					ps.setString(index++, brandcode);
				}
				if (pricelow > 0 || pricehigh > 0) {
					if (pricelow > 0 && pricehigh > 0) {
						ps.setInt(index++, pricelow);
						ps.setInt(index++, pricehigh);
					} else if (pricelow > 0)
						ps.setInt(index++, pricelow);
					else
						ps.setInt(index++, pricehigh);
				}
			}

			// 条件3：供货省平台
			if (!MyFormat.isStrNull(provider)) {
				ps.setString(index++, provider);
			} else if (!MyFormat.isStrNull(providerchanneltype)) {
				ps.setInt(index++, MyConstant.ORGTYPE_PROVPROVIDER);
				ps.setString(index++, channeltype);
			}

			// 条件4：必须排除免禁条件
			ps.setInt(index++, MyConstant.ORGTYPE_HALL);
			ps.setInt(index++, MyConstant.ORGTYPE_PROVPROVIDER);
			imeicount = ps.executeUpdate();

			// 二、插入禁/限售日志表
			if (imeicount >= 0) {
				sql = new StringBuilder();
				sql.append("INSERT INTO PRODUCTWARN (warnid,warntype,warnrange,areaid,county,hall,productsystem,producttype,");
				sql.append("brandcode,productid,channeltype,providerchanneltype,provider,pricelow,pricehigh,imeicount,");
				sql.append("salequantity,saledate,startdate,startopercode,standardamount,status,smsflag)");
				sql.append("VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,SYSDATE,?,?,?,'0')");
				ps = conn.prepareStatement(sql.toString());
				index = 1;
				ps.setInt(index++, warnid);
				ps.setString(index++, warntype);
				ps.setString(index++, warnrange);
				ps.setInt(index++, areaid);
				ps.setString(index++, county);
				ps.setString(index++, hall);
				ps.setString(index++, productsystem);
				ps.setString(index++, producttype);
				ps.setString(index++, brandcode);
				ps.setString(index++, productid);
				ps.setString(index++, channeltype);
				ps.setString(index++, providerchanneltype);
				ps.setString(index++, provider);
				ps.setInt(index++, pricelow);
				ps.setInt(index++, pricehigh);
				ps.setInt(index++, imeicount);
				ps.setInt(index++, salequantity);
				ps.setString(index++, saledate);
				ps.setString(index++, opercode);
				ps.setInt(index++, standardamount);
				ps.setString(index++, WARN_NEW);
				int count = ps.executeUpdate();
				if (count != 1) {
					imeicount = 0;
				}
			}

			if (imeicount > 0) {
				// 三、锁定BOSS的IMEI
				sql = new StringBuilder();
				sql.append("INSERT INTO BOSS_DEPOT (halldepotid,bossid,IMEI,mobileorgacode,providercode,cityshortname,doDATE,dotype) ");
				sql
						.append(" SELECT SEQ_BOSSDepot.NEXTVAL,(SELECT bossid FROM PRODUCTCOLORDETAIL pc WHERE pc.colorid=i.colorid AND (pc.areacode='200' OR pc.areacode=(SELECT a.areacode FROM area a,organiza h WHERE a.areaid=h.areaid AND h.orgacode=i.orgacode))),");
				sql.append(" IMEI,(SELECT mobileorgacode FROM MOBILEORGATOLOCAL m WHERE m.orgacode=i.orgacode),");
				sql
						.append(" Gd_Dyh.getCityProviderForBOSS(i.cityorgacode) cityorgacode,(SELECT a.shortname FROM area a,organiza h WHERE a.areaid=h.areaid AND h.orgacode=i.orgacode),SYSDATE,'3'  FROM IMEI i WHERE i.saleflag='1' AND i.IMEI IN (SELECT IMEI FROM PRODUCTWARNDETAIL WHERE warnid=?)");
				ps = conn.prepareStatement(sql.toString());
				ps.setInt(1, warnid);
				int count = ps.executeUpdate();
				if (imeicount != count) {
					imeicount = MyConstant.INT_NULL;
				} else {
					// 四、锁定服务厅IMEI表
					sql = new StringBuilder();
					sql
							.append(" UPDATE IMEI SET arrivalflag='0',sendflag='0' ,saleflag='0' WHERE saleflag='1' AND IMEI IN (SELECT IMEI FROM PRODUCTWARNDETAIL WHERE warnid=?)");
					ps = conn.prepareStatement(sql.toString());
					ps.setInt(1, warnid);
					count = ps.executeUpdate();
					if (imeicount != count) {
						imeicount = MyConstant.INT_NULL;
					} else {
						// 五、更新库存
						updateIMEIDepot(conn, warnid);
					}
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
			imeicount = MyConstant.INT_NULL;
		}
		return imeicount;
	}

	/**
	 * 终端解禁
	 * @author dyh 2010-9-8
	 * @param conn
	 * @param warnid
	 * @param opercode
	 * @return
	 */
	public static boolean cancelProductWarn(Connection conn, int warnid, String opercode) {
		// 必填参数
		if (warnid <= 0)
			return false;

		// 规格化参数
		if (MyFormat.isStrNull(opercode) || MyConstant.SELECT_FIRST_VALUE.equals(opercode))
			opercode = "ADMIN";

		boolean flag = false;
		try {
			if (!JDBCUtil.isConnected(conn)) {
				return flag;
			}
			StringBuilder sql = new StringBuilder();
			// 一、解禁BOSS的IMEI
			sql = new StringBuilder();
			sql.append("INSERT INTO BOSS_DEPOT (halldepotid,bossid,IMEI,mobileorgacode,providercode,cityshortname,doDATE,dotype) ");
			sql.append(" SELECT SEQ_BOSSDepot.NEXTVAL,(SELECT bossid FROM PRODUCTCOLORDETAIL pc WHERE pc.colorid=i.colorid AND ");
			sql
					.append(" (pc.areacode='200' OR pc.areacode=(SELECT a.areacode FROM AREA a,ORGANIZA h WHERE a.areaid=h.areaid AND h.orgacode=i.orgacode))) bossid,");
			sql.append(" i.IMEI,(SELECT mobileorgacode FROM MOBILEORGATOLOCAL m WHERE m.orgacode=i.orgacode) mobileorgacode,");
			sql.append(" Gd_Dyh.getCityProviderForBOSS(i.cityorgacode) cityorgacode,");
			sql.append(" (SELECT a.shortname FROM AREA a,ORGANIZA h WHERE a.areaid=h.areaid AND h.orgacode=i.orgacode) cityshortname,");
			sql.append(" SYSDATE,'1'  FROM IMEI i,PRODUCTWARN w,PRODUCTWARNDETAIL d WHERE  i.arrivalflag='0' AND i.sendflag='0' AND i.saleflag='0' ");
			sql.append(" AND i.IMEI=d.IMEI AND i.orgacode=d.orgacode AND w.warnid=d.warnid AND w.warnid=? AND w.status=?");

			PreparedStatement ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, warnid);
			ps.setString(2, WARN_NEW);
			ps.executeUpdate();

			// 二、解禁服务厅IMEI表
			sql = new StringBuilder();
			sql
					.append("UPDATE IMEI SET arrivalflag='1',sendflag='1' ,saleflag='1' WHERE (IMEI,orgacode) IN (SELECT d.IMEI,d.orgacode FROM PRODUCTWARNDETAIL d,PRODUCTWARN w WHERE d.warnid=w.warnid AND w.warnid=? AND w.status=?) AND arrivalflag='0' AND sendflag='0' AND saleflag='0' ");
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, warnid);
			ps.setString(2, WARN_NEW);
			int rowcount = ps.executeUpdate();

			// 三、解禁服务厅退货途中IMEI表
			sql = new StringBuilder();
			sql
					.append("UPDATE IMEI SET arrivalflag='1',sendflag='0' ,saleflag='0' WHERE (IMEI,orgacode) IN (SELECT d.IMEI,d.orgacode FROM PRODUCTWARNDETAIL d,PRODUCTWARN w WHERE d.warnid=w.warnid AND w.warnid=? AND w.status=?) AND arrivalflag='3' AND sendflag='0' AND saleflag='0'");
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, warnid);
			ps.setString(2, WARN_NEW);
			ps.executeUpdate();

			// 四、更新库存
			updateIMEIDepot(conn, warnid);

			// 五、修改日志表
			sql = new StringBuilder();
			sql.append("UPDATE PRODUCTWARN SET status=?,endopercode=?,enddate=SYSDATE WHERE warnid=? AND status=?");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, WARN_CANCEL);
			ps.setString(2, opercode);
			ps.setInt(3, warnid);
			ps.setString(4, WARN_NEW);
			flag = (ps.executeUpdate()) == 1;

		} catch (Exception e) {
			e.printStackTrace();
			flag = false;
		}
		return flag;
	}

	/**
	 * 根据停售warnid更新库存
	 * @author dyh 2011-9-25
	 * @param conn
	 * @param warnid
	 * @return
	 */
	public static boolean updateIMEIDepot(Connection conn, int warnid) {
		// 必填参数
		if (warnid <= 0)
			return false;
		boolean flag = false;
		try {
			if (!JDBCUtil.isConnected(conn)) {
				return flag;
			}
			StringBuilder sql = new StringBuilder();
			sql
					.append(" DELETE IMEIDEPOT i WHERE EXISTS (SELECT 1 FROM PRODUCTWARNDETAIL w WHERE w.warnid=? AND w.productid=i.productid AND w.orgacode=i.orgacode)");
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, warnid);
			ps.executeUpdate();

			sql = new StringBuilder();
			sql.append("INSERT INTO IMEIDEPOT (orgacode, productid, quanitity, providerorgacode, goodflag, validflag) ");
			sql.append(" select orgacode,productid,sum(amount),provorgacode,goodflag,validflag from ");
			sql.append(" (SELECT orgacode, productid, COUNT (IMEI) amount, NVL (provorgacode, '0') provorgacode, goodflag, case when arrivalflag='0' and sendflag='0' and saleflag = '0' then 1 else sendflag end validflag ");
			sql.append(" FROM IMEI i WHERE EXISTS (SELECT 1 FROM PRODUCTWARNDETAIL w WHERE w.warnid=? AND w.productid=i.productid AND w.orgacode=i.orgacode)");
			sql.append(" GROUP BY orgacode, productid, provorgacode, goodflag, sendflag, arrivalflag, saleflag) ");
			sql.append(" group by orgacode, productid, provorgacode, goodflag, validflag");
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, warnid);
			ps.executeUpdate();
			flag = true;

		} catch (Exception e) {
			e.printStackTrace();
			flag = false;
		}
		return flag;
	}
}